import pymysql


def get_industryField():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT industryField, COUNT(*) as count FROM 51job_table GROUP BY industryField'
    cur.execute(sql)
    datas = cur.fetchall()
    industryField = [i['industryField'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'industryField': industryField, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_companyName():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT companyName, COUNT(*) as count FROM 51job_table GROUP BY companyName HAVING count > 10 ORDER BY count desc'
    cur.execute(sql)
    datas = cur.fetchall()
    companyName = [i['companyName'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'companyName': companyName, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_city():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT SUBSTRING_INDEX(city, "·", 1) as city1, COUNT(*) as count FROM 51job_table GROUP BY city1 HAVING count > 5'
    cur.execute(sql)
    datas = cur.fetchall()
    city1 = [i['city1'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'city': city1, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_job_salary():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT salary, COUNT(*) as count FROM 51job_table GROUP BY salary HAVING count > 10'
    cur.execute(sql)
    datas = cur.fetchall()
    salary = [i['salary'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'salary': salary, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_positionName_salary():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT salary, positionName FROM 51job_table ORDER BY salary desc LIMIT 10'
    cur.execute(sql)
    datas = cur.fetchall()
    salary = [str(i['salary']).split('·')[0] for i in datas]
    salary1 = []
    for i in salary:
        min = int(i.split('-')[0].replace('千', '')) * 1000
        max = float(i.split('-')[1].replace('万', '')) * 10000
        salary1.append(int((max + min) / 2))
    positionName = [i['positionName'] for i in datas]
    data = {'salary': salary1, 'positionName': positionName}
    print(data)
    cur.close()
    conn.close()
    return data


if __name__ == '__main__':
    get_positionName_salary()
